using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using MySql.Data.MySqlClient;
using MT5Trade.Models.MarketData;

namespace MT5Trade.Tests.TestFramework
{
    /// <summary>
    /// 测试数据访问对象
    /// 严格遵守 generic-rule.md 规则
    /// 规则第280条：必须使用依赖注入
    /// </summary>
    public class TestDataDao : ITestDataDao
    {
        private readonly string _connectionString;
        private readonly ILogger<TestDataDao> _logger;

        public TestDataDao(
            IConfiguration configuration,
            ILogger<TestDataDao> logger)
        {
            _logger = logger;
            _connectionString = configuration.GetConnectionString("TestDatabase");
        }

        /// <summary>
        /// 查询市场数据
        /// 规则第168条：必须从MySQL表查询测试数据
        /// </summary>
        public async Task<List<MarketDataTestEntity>> QueryMarketDataAsync(MarketDataTestFilter filter)
        {
            var result = new List<MarketDataTestEntity>();

            try
            {
                using var connection = new MySqlConnection(_connectionString);
                await connection.OpenAsync();

                var sql = filter.BuildQuery();
                _logger.LogDebug($"执行SQL查询: {sql}");

                using var command = new MySqlCommand(sql, connection);
                using var reader = await command.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    var entity = new MarketDataTestEntity
                    {
                        Id = reader.GetInt64("id"),
                        Symbol = reader.GetString("symbol"),
                        Time = reader.GetDateTime("time"),
                        Open = reader.GetDecimal("open"),
                        High = reader.GetDecimal("high"),
                        Low = reader.GetDecimal("low"),
                        Close = reader.GetDecimal("close"),
                        Volume = reader.GetInt64("volume"),
                        IntervalType = reader.GetString("interval_type"),
                        DataSource = reader.GetString("data_source"),
                        CreatedAt = reader.GetDateTime("created_at")
                    };

                    result.Add(entity);
                }

                _logger.LogInformation($"查询到 {result.Count} 条市场数据");
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询市场数据失败");
                throw;
            }

            return result;
        }

        /// <summary>
        /// 插入市场数据
        /// </summary>
        public async Task<int> InsertMarketDataAsync(MarketDataTestEntity entity)
        {
            using var connection = new MySqlConnection(_connectionString);
            await connection.OpenAsync();

            var sql = @"
                INSERT INTO market_data_test
                (symbol, time, open, high, low, close, volume, interval_type, data_source, created_at)
                VALUES
                (@symbol, @time, @open, @high, @low, @close, @volume, @interval, @source, @created)";

            using var command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@symbol", entity.Symbol);
            command.Parameters.AddWithValue("@time", entity.Time);
            command.Parameters.AddWithValue("@open", entity.Open);
            command.Parameters.AddWithValue("@high", entity.High);
            command.Parameters.AddWithValue("@low", entity.Low);
            command.Parameters.AddWithValue("@close", entity.Close);
            command.Parameters.AddWithValue("@volume", entity.Volume);
            command.Parameters.AddWithValue("@interval", entity.IntervalType);
            command.Parameters.AddWithValue("@source", entity.DataSource);
            command.Parameters.AddWithValue("@created", entity.CreatedAt);

            return await command.ExecuteNonQueryAsync();
        }

        /// <summary>
        /// 插入性能测试结果
        /// </summary>
        public async Task<int> InsertPerformanceTestAsync(CachePerformanceTestEntity entity)
        {
            using var connection = new MySqlConnection(_connectionString);
            await connection.OpenAsync();

            var sql = @"
                INSERT INTO cache_performance_test
                (test_name, test_case_id, operation_type, data_count, file_size_kb,
                 duration_ms, memory_used_mb, cpu_usage_percent, speedup_ratio,
                 cache_hit_rate, test_time, test_environment)
                VALUES
                (@name, @case_id, @op_type, @count, @size, @duration, @memory,
                 @cpu, @speedup, @hit_rate, @time, @env)";

            using var command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@name", entity.TestName);
            command.Parameters.AddWithValue("@case_id", entity.TestCaseId);
            command.Parameters.AddWithValue("@op_type", entity.OperationType);
            command.Parameters.AddWithValue("@count", entity.DataCount);
            command.Parameters.AddWithValue("@size", entity.FileSizeKb ?? (object)DBNull.Value);
            command.Parameters.AddWithValue("@duration", entity.DurationMs);
            command.Parameters.AddWithValue("@memory", entity.MemoryUsedMb ?? (object)DBNull.Value);
            command.Parameters.AddWithValue("@cpu", entity.CpuUsagePercent ?? (object)DBNull.Value);
            command.Parameters.AddWithValue("@speedup", entity.SpeedupRatio ?? (object)DBNull.Value);
            command.Parameters.AddWithValue("@hit_rate", entity.CacheHitRate ?? (object)DBNull.Value);
            command.Parameters.AddWithValue("@time", entity.TestTime);
            command.Parameters.AddWithValue("@env", entity.TestEnvironment);

            var result = await command.ExecuteNonQueryAsync();

            _logger.LogInformation($"性能测试结果已记录: {entity.TestCaseId} - {entity.OperationType} - {entity.DurationMs}ms");

            return result;
        }

        /// <summary>
        /// 获取测试配置
        /// </summary>
        public async Task<Dictionary<string, string>> GetTestConfigurationAsync()
        {
            var config = new Dictionary<string, string>();

            using var connection = new MySqlConnection(_connectionString);
            await connection.OpenAsync();

            var sql = "SELECT config_key, config_value FROM test_configuration";
            using var command = new MySqlCommand(sql, connection);
            using var reader = await command.ExecuteReaderAsync();

            while (await reader.ReadAsync())
            {
                config[reader.GetString("config_key")] = reader.GetString("config_value");
            }

            return config;
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        public async Task ExecuteSqlAsync(string sql)
        {
            using var connection = new MySqlConnection(_connectionString);
            await connection.OpenAsync();

            using var command = new MySqlCommand(sql, connection);
            await command.ExecuteNonQueryAsync();
        }

        #region 套利测试数据方法

        /// <summary>
        /// 获取套利测试场景
        /// 规则第289条：所有测试数据必须来自MySQL表
        /// </summary>
        public async Task<List<ArbitrageTestScenario>> GetArbitrageTestScenariosAsync()
        {
            var result = new List<ArbitrageTestScenario>();

            using var connection = new MySqlConnection(_connectionString);
            await connection.OpenAsync();

            var sql = @"SELECT * FROM arbitrage_test_scenarios
                       WHERE scenario_type = 'NORMAL'
                       ORDER BY id";

            using var command = new MySqlCommand(sql, connection);
            using var reader = await command.ExecuteReaderAsync();

            while (await reader.ReadAsync())
            {
                result.Add(ReadArbitrageScenario(reader));
            }

            _logger.LogInformation($"获取 {result.Count} 个套利测试场景");
            return result;
        }

        /// <summary>
        /// 获取边界测试场景
        /// </summary>
        public async Task<List<ArbitrageTestScenario>> GetBoundaryTestScenariosAsync()
        {
            var result = new List<ArbitrageTestScenario>();

            using var connection = new MySqlConnection(_connectionString);
            await connection.OpenAsync();

            var sql = @"SELECT * FROM arbitrage_test_scenarios
                       WHERE scenario_type = 'BOUNDARY'
                       ORDER BY id";

            using var command = new MySqlCommand(sql, connection);
            using var reader = await command.ExecuteReaderAsync();

            while (await reader.ReadAsync())
            {
                result.Add(ReadArbitrageScenario(reader));
            }

            return result;
        }

        /// <summary>
        /// 获取失败测试场景
        /// </summary>
        public async Task<List<ArbitrageTestScenario>> GetFailureTestScenariosAsync()
        {
            var result = new List<ArbitrageTestScenario>();

            using var connection = new MySqlConnection(_connectionString);
            await connection.OpenAsync();

            var sql = @"SELECT * FROM arbitrage_test_scenarios
                       WHERE scenario_type = 'FAILURE'
                       ORDER BY id";

            using var command = new MySqlCommand(sql, connection);
            using var reader = await command.ExecuteReaderAsync();

            while (await reader.ReadAsync())
            {
                result.Add(ReadArbitrageScenario(reader));
            }

            return result;
        }

        private ArbitrageTestScenario ReadArbitrageScenario(System.Data.Common.DbDataReader reader)
        {
            return new ArbitrageTestScenario
            {
                Id = reader.GetInt32("id"),
                ScenarioName = reader.GetString("scenario_name"),
                ScenarioType = reader.GetString("scenario_type"),
                MainLossProbability = reader.GetDecimal("main_loss_probability"),
                ExpectedMove = reader.GetDecimal("expected_move"),
                Spread = reader.GetDecimal("spread"),
                BonusRemaining = reader.GetDecimal("bonus_remaining"),
                FollowerMarginLevel = reader.GetDecimal("follower_margin_level"),
                Volatility = reader.GetDecimal("volatility"),
                ExpectedGapMin = reader.GetDecimal("expected_gap_min"),
                ExpectedGapMax = reader.GetDecimal("expected_gap_max"),
                ShouldSucceed = reader.GetBoolean("should_succeed"),
                FailureReason = reader.IsDBNull("failure_reason") ? null : reader.GetString("failure_reason"),
                CreatedAt = reader.GetDateTime("created_at")
            };
        }

        /// <summary>
        /// 获取GARCH测试数据
        /// </summary>
        public async Task<List<GarchTestData>> GetGarchTestDataAsync()
        {
            var result = new List<GarchTestData>();

            using var connection = new MySqlConnection(_connectionString);
            await connection.OpenAsync();

            var sql = "SELECT * FROM garch_test_data ORDER BY id";

            using var command = new MySqlCommand(sql, connection);
            using var reader = await command.ExecuteReaderAsync();

            while (await reader.ReadAsync())
            {
                result.Add(new GarchTestData
                {
                    Id = reader.GetInt32("id"),
                    TestCaseName = reader.GetString("test_case_name"),
                    CurrentReturn = reader.GetDecimal("current_return"),
                    CurrentVolatility = reader.GetDecimal("current_volatility"),
                    ExpectedVolMin = reader.GetDecimal("expected_vol_min"),
                    ExpectedVolMax = reader.GetDecimal("expected_vol_max"),
                    GarchOmega = reader.GetDecimal("garch_omega"),
                    GarchAlpha = reader.GetDecimal("garch_alpha"),
                    GarchBeta = reader.GetDecimal("garch_beta"),
                    TestDate = reader.GetDateTime("test_date")
                });
            }

            return result;
        }

        /// <summary>
        /// 获取VaR测试数据
        /// </summary>
        public async Task<List<VarTestData>> GetVarTestDataAsync()
        {
            var result = new List<VarTestData>();

            using var connection = new MySqlConnection(_connectionString);
            await connection.OpenAsync();

            var sql = "SELECT * FROM var_test_data ORDER BY id";

            using var command = new MySqlCommand(sql, connection);
            using var reader = await command.ExecuteReaderAsync();

            while (await reader.ReadAsync())
            {
                result.Add(new VarTestData
                {
                    Id = reader.GetInt32("id"),
                    TestCaseName = reader.GetString("test_case_name"),
                    EntryPrice = reader.GetDecimal("entry_price"),
                    AccountEquity = reader.GetDecimal("account_equity"),
                    PositionSize = reader.GetDecimal("position_size"),
                    Volatility = reader.GetDecimal("volatility"),
                    ExpectedReturn = reader.GetDecimal("expected_return"),
                    ConfidenceLevel = reader.GetDecimal("confidence_level"),
                    MaxLossPercentage = reader.GetDecimal("max_loss_percentage"),
                    ExpectedStopLossMin = reader.IsDBNull("expected_stop_loss_min") ? null : reader.GetDecimal("expected_stop_loss_min"),
                    ExpectedStopLossMax = reader.IsDBNull("expected_stop_loss_max") ? null : reader.GetDecimal("expected_stop_loss_max")
                });
            }

            return result;
        }

        /// <summary>
        /// 获取市场条件测试数据
        /// </summary>
        public async Task<List<MarketConditionTestData>> GetMarketConditionTestDataAsync()
        {
            var result = new List<MarketConditionTestData>();

            using var connection = new MySqlConnection(_connectionString);
            await connection.OpenAsync();

            var sql = "SELECT * FROM market_condition_test_data ORDER BY id";

            using var command = new MySqlCommand(sql, connection);
            using var reader = await command.ExecuteReaderAsync();

            while (await reader.ReadAsync())
            {
                result.Add(new MarketConditionTestData
                {
                    Id = reader.GetInt32("id"),
                    ConditionName = reader.GetString("condition_name"),
                    IsAtStrongResistance = reader.GetBoolean("is_at_strong_resistance"),
                    IsAtStrongSupport = reader.GetBoolean("is_at_strong_support"),
                    IsBullish = reader.GetBoolean("is_bullish"),
                    RSI = reader.GetDecimal("rsi"),
                    TrendExhaustion = reader.GetDecimal("trend_exhaustion"),
                    ExpectedMove = reader.GetDecimal("expected_move"),
                    Spread = reader.GetDecimal("spread"),
                    BonusRemaining = reader.GetDecimal("bonus_remaining"),
                    FollowerMarginLevel = reader.GetDecimal("follower_margin_level"),
                    Volatility = reader.GetDecimal("volatility"),
                    ExpectedQualityMin = reader.GetDecimal("expected_quality_min"),
                    ExpectedQualityMax = reader.GetDecimal("expected_quality_max"),
                    ExpectedMainLossMin = reader.GetDecimal("expected_main_loss_min"),
                    ExpectedMainLossMax = reader.GetDecimal("expected_main_loss_max")
                });
            }

            return result;
        }

        /// <summary>
        /// 获取历史交易记录
        /// </summary>
        public async Task<List<TradeHistory>> GetTradeHistoryAsync(string batchId)
        {
            var result = new List<TradeHistory>();

            using var connection = new MySqlConnection(_connectionString);
            await connection.OpenAsync();

            var sql = @"SELECT * FROM arbitrage_trade_history
                       WHERE batch_id = @batchId
                       ORDER BY sequence_number";

            using var command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@batchId", batchId);
            using var reader = await command.ExecuteReaderAsync();

            while (await reader.ReadAsync())
            {
                result.Add(ReadTradeHistory(reader));
            }

            return result;
        }

        /// <summary>
        /// 获取最近的交易历史
        /// </summary>
        public async Task<List<TradeHistory>> GetRecentTradeHistoryAsync(int limit = 20)
        {
            var result = new List<TradeHistory>();

            using var connection = new MySqlConnection(_connectionString);
            await connection.OpenAsync();

            var sql = @"SELECT * FROM arbitrage_trade_history
                       ORDER BY trade_time DESC
                       LIMIT @limit";

            using var command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@limit", limit);
            using var reader = await command.ExecuteReaderAsync();

            while (await reader.ReadAsync())
            {
                result.Add(ReadTradeHistory(reader));
            }

            return result;
        }

        private TradeHistory ReadTradeHistory(System.Data.Common.DbDataReader reader)
        {
            return new TradeHistory
            {
                Id = reader.GetInt32("id"),
                BatchId = reader.GetString("batch_id"),
                SequenceNumber = reader.GetInt32("sequence_number"),
                TradeTime = reader.GetDateTime("trade_time"),
                IsProfitable = reader.GetBoolean("is_profitable"),
                GapUsed = reader.GetDecimal("gap_used"),
                BaseGap = reader.GetDecimal("base_gap"),
                AdjustmentFactor = reader.GetDecimal("adjustment_factor"),
                WinRate = reader.IsDBNull("win_rate") ? null : reader.GetDecimal("win_rate"),
                ConsecutiveLosses = reader.GetInt32("consecutive_losses")
            };
        }

        /// <summary>
        /// 获取KKT验证数据
        /// </summary>
        public async Task<List<KktVerificationData>> GetKktVerificationDataAsync()
        {
            var result = new List<KktVerificationData>();

            using var connection = new MySqlConnection(_connectionString);
            await connection.OpenAsync();

            var sql = "SELECT * FROM kkt_verification_data ORDER BY id";

            using var command = new MySqlCommand(sql, connection);
            using var reader = await command.ExecuteReaderAsync();

            while (await reader.ReadAsync())
            {
                result.Add(new KktVerificationData
                {
                    Id = reader.GetInt32("id"),
                    TestCaseName = reader.GetString("test_case_name"),
                    GapValue = reader.GetDecimal("gap_value"),
                    Gradient = reader.GetDecimal("gradient"),
                    Lambda1 = reader.IsDBNull("lambda1") ? null : reader.GetDecimal("lambda1"),
                    Lambda2 = reader.IsDBNull("lambda2") ? null : reader.GetDecimal("lambda2"),
                    StationaritySatisfied = reader.IsDBNull("stationarity_satisfied") ? null : reader.GetBoolean("stationarity_satisfied"),
                    PrimalFeasible = reader.IsDBNull("primal_feasible") ? null : reader.GetBoolean("primal_feasible"),
                    DualFeasible = reader.IsDBNull("dual_feasible") ? null : reader.GetBoolean("dual_feasible"),
                    ComplementarySlack = reader.IsDBNull("complementary_slack") ? null : reader.GetBoolean("complementary_slack"),
                    KktSatisfied = reader.IsDBNull("kkt_satisfied") ? null : reader.GetBoolean("kkt_satisfied"),
                    Tolerance = reader.GetDecimal("tolerance")
                });
            }

            return result;
        }

        /// <summary>
        /// 获取边界条件测试用例
        /// </summary>
        public async Task<List<BoundaryTestCase>> GetBoundaryTestCasesAsync()
        {
            var result = new List<BoundaryTestCase>();

            using var connection = new MySqlConnection(_connectionString);
            await connection.OpenAsync();

            var sql = "SELECT * FROM boundary_test_cases ORDER BY id";

            using var command = new MySqlCommand(sql, connection);
            using var reader = await command.ExecuteReaderAsync();

            while (await reader.ReadAsync())
            {
                result.Add(new BoundaryTestCase
                {
                    Id = reader.GetInt32("id"),
                    TestName = reader.GetString("test_name"),
                    TestType = reader.GetString("test_type"),
                    ParamName = reader.GetString("param_name"),
                    ParamValue = reader.IsDBNull("param_value") ? null : reader.GetDecimal("param_value"),
                    ShouldThrowException = reader.GetBoolean("should_throw_exception"),
                    ExpectedExceptionType = reader.IsDBNull("expected_exception_type") ? null : reader.GetString("expected_exception_type"),
                    ExpectedBehavior = reader.IsDBNull("expected_behavior") ? null : reader.GetString("expected_behavior")
                });
            }

            return result;
        }

        /// <summary>
        /// 插入测试执行结果
        /// </summary>
        public async Task InsertTestResultAsync(TestExecutionResult result)
        {
            using var connection = new MySqlConnection(_connectionString);
            await connection.OpenAsync();

            var sql = @"
                INSERT INTO test_execution_results
                (test_class, test_method, test_case_id, input_params,
                 actual_result, expected_result, deviation, passed,
                 error_message, execution_time_ms)
                VALUES
                (@class, @method, @caseId, @params, @actual, @expected,
                 @deviation, @passed, @error, @time)";

            using var command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@class", result.TestClass);
            command.Parameters.AddWithValue("@method", result.TestMethod);
            command.Parameters.AddWithValue("@caseId", result.TestCaseId ?? (object)DBNull.Value);
            command.Parameters.AddWithValue("@params", result.InputParams ?? (object)DBNull.Value);
            command.Parameters.AddWithValue("@actual", result.ActualResult ?? (object)DBNull.Value);
            command.Parameters.AddWithValue("@expected", result.ExpectedResult ?? (object)DBNull.Value);
            command.Parameters.AddWithValue("@deviation", result.Deviation ?? (object)DBNull.Value);
            command.Parameters.AddWithValue("@passed", result.Passed);
            command.Parameters.AddWithValue("@error", result.ErrorMessage ?? (object)DBNull.Value);
            command.Parameters.AddWithValue("@time", result.ExecutionTimeMs ?? (object)DBNull.Value);

            await command.ExecuteNonQueryAsync();

            _logger.LogInformation($"测试结果已记录: {result.TestClass}.{result.TestMethod} - {(result.Passed ? "通过" : "失败")}");
        }

        /// <summary>
        /// 获取测试执行结果
        /// </summary>
        public async Task<List<TestExecutionResult>> GetTestResultsAsync(string testClass, string testMethod)
        {
            var result = new List<TestExecutionResult>();

            using var connection = new MySqlConnection(_connectionString);
            await connection.OpenAsync();

            var sql = @"SELECT * FROM test_execution_results
                       WHERE test_class = @class AND test_method = @method
                       ORDER BY executed_at DESC";

            using var command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@class", testClass);
            command.Parameters.AddWithValue("@method", testMethod);
            using var reader = await command.ExecuteReaderAsync();

            while (await reader.ReadAsync())
            {
                result.Add(new TestExecutionResult
                {
                    Id = reader.GetInt32("id"),
                    TestClass = reader.GetString("test_class"),
                    TestMethod = reader.GetString("test_method"),
                    TestCaseId = reader.IsDBNull("test_case_id") ? null : reader.GetInt32("test_case_id"),
                    InputParams = reader.IsDBNull("input_params") ? null : reader.GetString("input_params"),
                    ActualResult = reader.IsDBNull("actual_result") ? null : reader.GetDecimal("actual_result"),
                    ExpectedResult = reader.IsDBNull("expected_result") ? null : reader.GetDecimal("expected_result"),
                    Deviation = reader.IsDBNull("deviation") ? null : reader.GetDecimal("deviation"),
                    Passed = reader.GetBoolean("passed"),
                    ErrorMessage = reader.IsDBNull("error_message") ? null : reader.GetString("error_message"),
                    ExecutionTimeMs = reader.IsDBNull("execution_time_ms") ? null : reader.GetInt32("execution_time_ms"),
                    ExecutedAt = reader.GetDateTime("executed_at")
                });
            }

            return result;
        }

        /// <summary>
        /// 初始化测试数据
        /// </summary>
        public async Task<bool> InitializeTestDataAsync()
        {
            try
            {
                _logger.LogInformation("开始初始化测试数据...");

                // 执行存储过程插入测试数据
                using var connection = new MySqlConnection(_connectionString);
                await connection.OpenAsync();

                using var command = new MySqlCommand("CALL InsertArbitrageTestData()", connection);
                await command.ExecuteNonQueryAsync();

                _logger.LogInformation("测试数据初始化成功");
                return true;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "初始化测试数据失败");
                return false;
            }
        }

        /// <summary>
        /// 清理测试数据
        /// </summary>
        public async Task<bool> CleanupTestDataAsync()
        {
            try
            {
                _logger.LogInformation("开始清理测试数据...");

                using var connection = new MySqlConnection(_connectionString);
                await connection.OpenAsync();

                // 清理所有测试表数据
                var tables = new[]
                {
                    "test_execution_results",
                    "boundary_test_cases",
                    "kkt_verification_data",
                    "arbitrage_trade_history",
                    "market_condition_test_data",
                    "var_test_data",
                    "garch_test_data",
                    "arbitrage_test_scenarios"
                };

                foreach (var table in tables)
                {
                    using var command = new MySqlCommand($"TRUNCATE TABLE {table}", connection);
                    await command.ExecuteNonQueryAsync();
                }

                _logger.LogInformation("测试数据清理成功");
                return true;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "清理测试数据失败");
                return false;
            }
        }

        #endregion

        #region 新增的方法实现

        /// <summary>
        /// 获取连续亏损测试序列
        /// </summary>
        public async Task<List<ConsecutiveLossTestSequence>> GetConsecutiveLossTestSequencesAsync()
        {
            var sequences = new List<ConsecutiveLossTestSequence>();

            try
            {
                using var connection = new MySqlConnection(_connectionString);
                await connection.OpenAsync();

                var sql = @"
                    SELECT id, sequence_name, base_gap, expected_final_consecutive_losses, description
                    FROM consecutive_loss_test_sequences
                    WHERE is_active = 1
                    ORDER BY id";

                using var command = new MySqlCommand(sql, connection);
                using var reader = await command.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    sequences.Add(new ConsecutiveLossTestSequence
                    {
                        Id = reader.GetInt32("id"),
                        SequenceName = reader.GetString("sequence_name"),
                        BaseGap = reader.GetDecimal("base_gap"),
                        ExpectedFinalConsecutiveLosses = reader.GetInt32("expected_final_consecutive_losses"),
                        Description = reader.IsDBNull("description") ? null : reader.GetString("description")
                    });
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取连续亏损测试序列失败");
                throw;
            }

            return sequences;
        }

        /// <summary>
        /// 获取交易序列
        /// </summary>
        public async Task<List<TradeSequenceItem>> GetTradeSequenceAsync(int sequenceId)
        {
            var items = new List<TradeSequenceItem>();

            try
            {
                using var connection = new MySqlConnection(_connectionString);
                await connection.OpenAsync();

                var sql = @"
                    SELECT id, sequence_id, order_index, is_win, interval_seconds
                    FROM trade_sequence_items
                    WHERE sequence_id = @sequenceId
                    ORDER BY order_index";

                using var command = new MySqlCommand(sql, connection);
                command.Parameters.AddWithValue("@sequenceId", sequenceId);
                using var reader = await command.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    items.Add(new TradeSequenceItem
                    {
                        Id = reader.GetInt32("id"),
                        SequenceId = reader.GetInt32("sequence_id"),
                        OrderIndex = reader.GetInt32("order_index"),
                        IsWin = reader.GetBoolean("is_win"),
                        IntervalSeconds = reader.GetDecimal("interval_seconds")
                    });
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"获取交易序列失败: sequenceId={sequenceId}");
                throw;
            }

            return items;
        }

        /// <summary>
        /// 获取动态调整场景
        /// </summary>
        public async Task<List<DynamicAdjustmentScenario>> GetDynamicAdjustmentScenariosAsync()
        {
            var scenarios = new List<DynamicAdjustmentScenario>();

            try
            {
                using var connection = new MySqlConnection(_connectionString);
                await connection.OpenAsync();

                var sql = @"
                    SELECT id, scenario_name, base_gap, target_win_rate,
                           expected_adjustment_factor, expected_min_gap, expected_max_gap
                    FROM dynamic_adjustment_scenarios
                    WHERE is_active = 1
                    ORDER BY id";

                using var command = new MySqlCommand(sql, connection);
                using var reader = await command.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    scenarios.Add(new DynamicAdjustmentScenario
                    {
                        Id = reader.GetInt32("id"),
                        ScenarioName = reader.GetString("scenario_name"),
                        BaseGap = reader.GetDecimal("base_gap"),
                        TargetWinRate = reader.GetDecimal("target_win_rate"),
                        ExpectedAdjustmentFactor = reader.GetDecimal("expected_adjustment_factor"),
                        ExpectedMinGap = reader.GetDecimal("expected_min_gap"),
                        ExpectedMaxGap = reader.GetDecimal("expected_max_gap")
                    });
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取动态调整场景失败");
                throw;
            }

            return scenarios;
        }

        /// <summary>
        /// 获取场景的历史交易
        /// </summary>
        public async Task<List<HistoricalTrade>> GetHistoricalTradesForScenarioAsync(int scenarioId)
        {
            var trades = new List<HistoricalTrade>();

            try
            {
                using var connection = new MySqlConnection(_connectionString);
                await connection.OpenAsync();

                var sql = @"
                    SELECT id, scenario_id, is_win, interval_seconds, pnl
                    FROM historical_trades
                    WHERE scenario_id = @scenarioId
                    ORDER BY id";

                using var command = new MySqlCommand(sql, connection);
                command.Parameters.AddWithValue("@scenarioId", scenarioId);
                using var reader = await command.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    trades.Add(new HistoricalTrade
                    {
                        Id = reader.GetInt32("id"),
                        ScenarioId = reader.GetInt32("scenario_id"),
                        IsWin = reader.GetBoolean("is_win"),
                        IntervalSeconds = reader.GetDecimal("interval_seconds"),
                        PnL = reader.GetDecimal("pnl")
                    });
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"获取历史交易失败: scenarioId={scenarioId}");
                throw;
            }

            return trades;
        }

        /// <summary>
        /// 获取赠金消耗场景
        /// </summary>
        public async Task<List<BonusConsumptionScenario>> GetBonusConsumptionScenariosAsync()
        {
            var scenarios = new List<BonusConsumptionScenario>();

            try
            {
                using var connection = new MySqlConnection(_connectionString);
                await connection.OpenAsync();

                var sql = @"
                    SELECT id, scenario_name, bonus_remaining, main_loss_probability,
                           expected_move, spread, follower_margin_level, current_volatility,
                           expected_min_gap, expected_max_gap
                    FROM bonus_consumption_scenarios
                    WHERE is_active = 1
                    ORDER BY bonus_remaining DESC";

                using var command = new MySqlCommand(sql, connection);
                using var reader = await command.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    scenarios.Add(new BonusConsumptionScenario
                    {
                        Id = reader.GetInt32("id"),
                        ScenarioName = reader.GetString("scenario_name"),
                        BonusRemaining = reader.GetDecimal("bonus_remaining"),
                        MainLossProbability = reader.GetDecimal("main_loss_probability"),
                        ExpectedMove = reader.GetDecimal("expected_move"),
                        Spread = reader.GetDecimal("spread"),
                        FollowerMarginLevel = reader.GetDecimal("follower_margin_level"),
                        CurrentVolatility = reader.GetDecimal("current_volatility"),
                        ExpectedMinGap = reader.GetDecimal("expected_min_gap"),
                        ExpectedMaxGap = reader.GetDecimal("expected_max_gap")
                    });
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取赠金消耗场景失败");
                throw;
            }

            return scenarios;
        }

        /// <summary>
        /// 获取保证金水平场景
        /// </summary>
        public async Task<List<MarginLevelScenario>> GetMarginLevelScenariosAsync()
        {
            var scenarios = new List<MarginLevelScenario>();

            try
            {
                using var connection = new MySqlConnection(_connectionString);
                await connection.OpenAsync();

                var sql = @"
                    SELECT id, scenario_name, margin_level, main_loss_probability,
                           expected_move, spread, bonus_remaining, current_volatility,
                           expected_min_gap, expected_max_gap
                    FROM margin_level_scenarios
                    WHERE is_active = 1
                    ORDER BY margin_level DESC";

                using var command = new MySqlCommand(sql, connection);
                using var reader = await command.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    scenarios.Add(new MarginLevelScenario
                    {
                        Id = reader.GetInt32("id"),
                        ScenarioName = reader.GetString("scenario_name"),
                        MarginLevel = reader.GetDecimal("margin_level"),
                        MainLossProbability = reader.GetDecimal("main_loss_probability"),
                        ExpectedMove = reader.GetDecimal("expected_move"),
                        Spread = reader.GetDecimal("spread"),
                        BonusRemaining = reader.GetDecimal("bonus_remaining"),
                        CurrentVolatility = reader.GetDecimal("current_volatility"),
                        ExpectedMinGap = reader.GetDecimal("expected_min_gap"),
                        ExpectedMaxGap = reader.GetDecimal("expected_max_gap")
                    });
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取保证金水平场景失败");
                throw;
            }

            return scenarios;
        }

        #endregion
    }
}